package com.pactera.controller;

import java.util.List;
import java.util.stream.Collectors;

import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.pactera.entity.GoodDTO;
import com.pactera.entity.GoodInfoBean;
import com.pactera.entity.QGoodInfoBean;
import com.pactera.entity.QGoodTypeBean;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.JPAExpressions;
import com.querydsl.jpa.impl.JPAQueryFactory;

@RestController
public class GoodController {
	
	@Autowired
    private EntityManager entityManager;
	
	//查询工厂实体
    private JPAQueryFactory queryFactory;
    
    @PostConstruct
    public void initFactory() {
    	System.out.println("开始实例化JPAQueryFactory"); 
    	queryFactory = new JPAQueryFactory(entityManager);
    }
    
    @RequestMapping(value = "/selectByType")
    public List<GoodInfoBean> selectByType (
    		@RequestParam(value = "typeId") Long typeId//类型编号
    		) {
    	//商品查询实体
    	QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;
    	//商品类型查询实体
    	QGoodTypeBean _Q_good_type = QGoodTypeBean.goodTypeBean;
    	return queryFactory .select(_Q_good) .from(_Q_good,_Q_good_type) .where(
    			//为两个实体关联查询 相当于left join
    			_Q_good.typeId.eq(_Q_good_type.id) .and(
    					//查询指定typeid的商品 
    					_Q_good_type.id.eq(typeId) ) )
    			//根据排序字段倒序 
    			.orderBy(_Q_good.order.desc())
    			//执行查询 
    			.fetch();
    }
    
    //QueryDSL & Projections
    @RequestMapping(value = "/selectWithQueryDSL") 
    public List<GoodDTO> selectWithQueryDSL() {
    	//商品基本信息 
    	QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;
    	//商品类型 
    	//Projections是QueryDSL内置针对处理自定义返回结果集的解决方案，里面包含了构造函数、实体、字段等处理方法
    	QGoodTypeBean _Q_good_type = QGoodTypeBean.goodTypeBean;
    	return queryFactory.select(Projections.bean( GoodDTO.class,
    			//返回自定义实体的类型 
    			_Q_good.id, _Q_good.price, _Q_good.title, _Q_good.unit, _Q_good_type.name.as("typeName"),//使用别名对应dto内的typeName 
    			_Q_good_type.id.as("typeId")//使用别名对应dto内的typeId 
    			) ) .from(_Q_good,_Q_good_type)//构建两表笛卡尔集 
    			.where(_Q_good.typeId.eq(_Q_good_type.id))//关联两表 
    			.orderBy(_Q_good.order.desc())//倒序 
    			.fetch();
    }
    
    //QueryDSL & Collection
    @RequestMapping(value = "/selectWithStream") 
    public List<GoodDTO> selectWithStream() {
    	//商品基本信息 
    	QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean; 
    	//商品类型 
    	QGoodTypeBean _Q_good_type = QGoodTypeBean.goodTypeBean; 
    	return queryFactory.select( 
    			_Q_good.id, 
    			_Q_good.price, 
    			_Q_good.title, 
    			_Q_good.unit, 
    			_Q_good_type.name, 
    			_Q_good_type.id ) 
    			.from(_Q_good,_Q_good_type)
    			//构建两表笛卡尔集 
    			.where(_Q_good.typeId.eq(_Q_good_type.id))
    			//关联两表 
    			.orderBy(_Q_good.order.desc())//倒序 
    			.fetch()
    			.stream() //转换集合内的数据 
    			.map(tuple -> { //创建商品dto 
    				GoodDTO dto = new GoodDTO(); 
    				//设置商品编号 
    				dto.setId(tuple.get(_Q_good.id)); 
    				//设置商品价格 
    				dto.setPrice(tuple.get(_Q_good.price)); 
    				//设置商品标题
    				dto.setTitle(tuple.get(_Q_good.title)); 
    				//设置单位 
    				dto.setUnit(tuple.get(_Q_good.unit)); 
    				//设置类型编号 
    				dto.setTypeId(tuple.get(_Q_good_type.id)); 
    				//设置类型名称 
    				dto.setTypeName(tuple.get(_Q_good_type.name)); 
    				//返回本次构建的dto 
    				return dto; 
    				}) 
    			//返回集合并且转换为List<GoodDTO> 
    			.collect(Collectors.toList());
    	}
    
    /**
     * 中文在like中似乎不太好使，待研究
     * @return
     */
    @RequestMapping(value = "/childLikeSelect")
    public List<GoodInfoBean> childLikeSelect() {
    	//商品基本信息查询实体
    	QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;
    	//商品类型查询实体
    	QGoodTypeBean _Q_good_type = QGoodTypeBean.goodTypeBean;
    	
    	return queryFactory.selectFrom(_Q_good)//查询商品基本信息表
    			.where(//查询类型名称包含“蔬菜”
    					_Q_good.typeId.in(JPAExpressions
    							.select(_Q_good_type.id)
    							.from(_Q_good_type)
    							.where(_Q_good_type.name.like("%c%"))))
    			.fetch();
    }
    
    @RequestMapping(value = "/childEqSelect")
    public List<GoodInfoBean> childEqSelect() {
    	//商品基本信息查询实体
    	QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;
    	return queryFactory .selectFrom(_Q_good) //查询价格最大的商品列表
    			.where(_Q_good.price.eq(JPAExpressions
    					.select(_Q_good.price.max()).from(_Q_good)))
    			.fetch();
    }
    
    @RequestMapping(value = "/childGtAvgSelect") 
    public List<GoodInfoBean> childGtAvgSelect() {
    	//商品基本信息查询实体
    	QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;
    	return queryFactory .selectFrom(_Q_good)
    			//查询价格高于平均价的商品列表
    			.where(_Q_good.price.gt(JPAExpressions
    					.select(_Q_good.price.avg())
    					.from(_Q_good)))
    			.fetch();
    }
    
}
